﻿/*=======================================================================================================================
=======================================================================================================================*/
CREATE TABLE TSL_StagingTableForPerfmonImports (
    [instanceId]      INT            NOT NULL,
    [machineName]     NVARCHAR (255) NOT NULL,
    [categoryName]    NVARCHAR (255) NOT NULL,
    [counterName]     NVARCHAR (255) NOT NULL,
    [instanceName]    NVARCHAR (255) NOT NULL,
    [cumulativeValue] REAL           NULL
);
GO


/*=======================================================================================================================
=======================================================================================================================*/
CREATE VIEW MTSL_View_PerfmonInstanceNamesAndIds AS
---------------------------------------------------------------------------------
	SELECT 
		instance.LoadTestRunId
		,instance.InstanceId
		,(
			'\\' + category.MachineName
			+ '\' + category.CategoryName
			+	case instance.InstanceName when 'systemdiagnosticsperfcounterlibsingleinstance'
						then ''
						else '(' + instance.InstanceName  + ')'
						end
			+ '\' + counter.CounterName
		) AS InstanceName
		,category.MachineName
		,category.CategoryName
		,counter.CounterName
		,(case instance.InstanceName when 'systemdiagnosticsperfcounterlibsingleinstance'
						then ''
						else instance.InstanceName
						end) AS Instance

	FROM LoadTestPerformanceCounterCategory AS category 

	INNER JOIN LoadTestPerformanceCounter AS counter 
		ON category.LoadTestRunId = counter.LoadTestRunId
		AND category.CounterCategoryId = counter.CounterCategoryId

	INNER JOIN LoadTestPerformanceCounterInstance AS instance 
		ON counter.CounterId = instance.CounterId
		AND counter.LoadTestRunId = instance.LoadTestRunId
GO


/*=======================================================================================================================
=======================================================================================================================*/
CREATE PROCEDURE TSL_prc_GetStartingValuesForImportingPerfmon
	@RunId  int
AS
BEGIN
	DECLARE @tt TABLE(
		FirstIntervalTime DATETIME
		,LastIntervalTime DATETIME
		,CounterCategoryId INT
		,CounterId INT
		,InstanceId INT
		,IntervalSpan INT
		,IntervalQty INT
		)

	INSERT INTO @tt (FirstIntervalTime) 
		SELECT TOP 1 IntervalStartTime FROM LoadTestRunInterval
		WHERE LoadTestRunId = @RunId AND IntervalStartTime != IntervalEndTime
		ORDER BY IntervalStartTime ASC

	UPDATE @tt SET LastIntervalTime = (SELECT TOP 1 IntervalStartTime FROM LoadTestRunInterval
		WHERE LoadTestRunId = @RunId AND IntervalStartTime != IntervalEndTime
		ORDER BY IntervalStartTime DESC)

	UPDATE @tt SET CounterCategoryId = (SELECT TOP 1 CounterCategoryId FROM LoadTestPerformanceCounterCategory 
		WHERE LoadTestRunId = @RunId ORDER BY CounterCategoryId DESC) + 1

	UPDATE @tt SET CounterId = (SELECT TOP 1 CounterId FROM LoadTestPerformanceCounter 
		WHERE LoadTestRunId = @RunId ORDER BY CounterId DESC) + 1

	UPDATE @tt SET InstanceId = (SELECT TOP 1 InstanceId FROM LoadTestPerformanceCounterInstance 
		WHERE LoadTestRunId = @RunId ORDER BY InstanceId DESC) + 1

	UPDATE @tt SET IntervalQty = (SELECT COUNT(TestRunIntervalId) FROM LoadTestRunInterval 
		WHERE LoadTestRunId = @RunId )

	UPDATE @tt SET IntervalSpan = (SELECT DATEDIFF(second,IntervalStartTime ,IntervalEndTime) FROM LoadTestRunInterval 
		WHERE TestRunIntervalId = 1 AND LoadTestRunId = @RunId)

	SELECT * FROM @tt
END
GO


/*=======================================================================================================================
=======================================================================================================================*/
CREATE PROCEDURE TSL_prc_UpdatePerfmonTablesFromImport
	@RunId  int
AS
BEGIN
	-- Update the CounterCategory Table -----------------------------------------------------------
		DECLARE @catId INT
		SET @catId = (SELECT TOP 1 CounterCategoryId FROM LoadTestPerformanceCounterCategory WHERE LoadTestRunId = @RunId ORDER BY CounterCategoryId DESC)
		DECLARE @TimeStamp BIGINT = 0
 
		INSERT INTO LoadTestPerformanceCounterCategory([LoadTestRunId],[CounterCategoryId],[CategoryName],[MachineName],[StartTimeStamp100nSec])
		SELECT @RunId, @catId + ROW_NUMBER() OVER (ORDER BY @catId) AS [identity]
			, categoryName, machineName, @TimeStamp FROM TSL_StagingTableForPerfmonImports GROUP BY machineName, categoryName


	-- Update the Counter Table -------------------------------------------------------------------
	DECLARE @counterId INT
		SET @counterId = (SELECT TOP 1 CounterId FROM LoadTestPerformanceCounter WHERE LoadTestRunId = @RunId ORDER BY CounterId DESC)

	INSERT INTO LoadTestPerformanceCounter (LoadTestRunId, CounterCategoryId, CounterId, CounterName, HigherIsBetter)
		SELECT @RunId, b.CounterCategoryId, @counterId + ROW_NUMBER() OVER (ORDER BY @counterId) AS [identity]
			,a.counterName, NULL FROM TSL_StagingTableForPerfmonImports AS a
		INNER JOIN LoadTestPerformanceCounterCategory AS b
			ON b.LoadTestRunId = @RunId 
			AND a.machineName = b.MachineName 
			AND b.CategoryName = a.categoryName
		GROUP BY a.counterName, b.CounterCategoryId



	-- Update the Instance Table ------------------------------------------------------------------
	INSERT INTO LoadTestPerformanceCounterInstance (LoadTestRunId, CounterId, InstanceId, LoadTestItemId, InstanceName, CumulativeValue, OverallThresholdRuleResult)
		SELECT @RunId, b.CounterId, a.InstanceId, NULL, a.instanceName, a.cumulativeValue, NULL
		FROM TSL_StagingTableForPerfmonImports AS a
		INNER JOIN LoadTestPerformanceCounter AS b 
			ON b.LoadTestRunId = @RunId
			AND a.counterName = b.CounterName
		INNER JOIN LoadTestPerformanceCounterCategory AS c
			ON c.LoadTestRunId = @RunId
			AND a.machineName = c.MachineName 
			AND a.categoryName = c.CategoryName
		WHERE b.CounterCategoryId = c.CounterCategoryId

	--IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'TSL_prc_UpdateScoreCardMachineCpuTable') AND type in (N'P'))
	--	EXEC TSL_prc_UpdateScoreCardMachineCpuTable

	--IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'TSL_prc_UpdateScoreCardMachineAvailableMBTable') AND type in (N'P'))
	--	EXEC TSL_prc_UpdateScoreCardMachineAvailableMBTable
END
GO


/*=======================================================================================================================
=======================================================================================================================*/
CREATE PROCEDURE TSL_prc_PerfCounterExportToCSV
	@RunId nvarchar(10),
	@InstanceName nvarchar(1024)
AS

	DECLARE @CounterName nvarchar(max), @CounterNameColumns nvarchar(max)

	--Get List of columns to use in query. Shove them into a single long XML string
	SELECT @CounterNameColumns = (
					SELECT ', [' + REPLACE(InstanceName, ']', ']]') + ']' FROM MTSL_View_PerfmonInstanceNamesAndIds
					WHERE LoadTestRunId = @RunId
					AND InstanceName LIKE @InstanceName
					AND CategoryName NOT LIKE 'LoadTest%'
			FOR XML PATH(''))


	--Make a copy of the list WITHOUT the comma at the very beginning of the string
	SELECT @CounterName = RIGHT(@CounterNameColumns, LEN(@CounterNameColumns) - 1)


	-- Use the previous strings to build the query string that can be pivoted
	DECLARE @SQL nvarchar(max)
		SELECT @SQL = N'
		select
		IntervalStartTime AS [(PDH-CSV 4.0) (Eastern Daylight Time)(240)]' + 
		--IntervalStartTime' + 
		@CounterNameColumns + '
		from (
			select
				interval.IntervalStartTime,
				MTSL_View_PerfmonInstanceNamesAndIds.InstanceName,
				countersample.ComputedValue
			FROM 
				MTSL_View_PerfmonInstanceNamesAndIds

			INNER JOIN LoadTestPerformanceCounterSample AS countersample 
				ON countersample.InstanceId = MTSL_View_PerfmonInstanceNamesAndIds.InstanceId
				AND countersample.LoadTestRunId = MTSL_View_PerfmonInstanceNamesAndIds.LoadTestRunId

			INNER JOIN LoadTestRunInterval AS interval 
				ON interval.LoadTestRunId = countersample.LoadTestRunId
				AND interval.TestRunIntervalId = countersample.TestRunIntervalId

			WHERE
				MTSL_View_PerfmonInstanceNamesAndIds.LoadTestRunId = ' + @RunId + '
				AND 
				MTSL_View_PerfmonInstanceNamesAndIds.InstanceName LIKE '''+@InstanceName+'''
		) Data
		PIVOT (
		SUM(ComputedValue)
		FOR InstanceName
		IN (
			' + @CounterName + '
		)
		) PivotTable
		ORDER BY IntervalStartTime ASC
		'

		print @SQL
	-- Execute the generated query
	exec sp_executesql @SQL
GO

